package net.sea.commons.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Properties;

import org.apache.ibatis.jdbc.ScriptRunner;


/**
 * 数据库操作工具类
 * 
 * @author 华成伍
 *
 */
public class DBUtils {
	private static ClassLoader classLoader;

	/**
	 * 获取数据库连接
	 * 
	 * @param url
	 *            jdbc数据库连接URL
	 * @param username
	 *            用户名
	 * @param password
	 *            密码
	 * @param driverClass
	 *            驱动程序类
	 * @return 数据库连接
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	public static Connection getConnection(String url, String username,
			String password, String driverClass) throws SQLException,
			ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		Class<?> loadClass = classLoader.loadClass(driverClass);
		// 实例化数据库驱动对象
		Driver driver = (Driver) loadClass.newInstance();
		// 获取Class所属的jar包
		// String path = loadClass.getProtectionDomain().getCodeSource()
		// .getLocation().getFile();
		// System.out.println(path);
		// 设置数据库连接的属性
		Properties props = new Properties();
		props.put("user", username);
		props.put("password", password);
		// 获取数据库连接
		return driver.connect(url, props);
	}

	/**
	 * 加载驱动程序
	 * 
	 * @param urls
	 *            驱动URL
	 * @throws Exception
	 */
	public static void loadClass(URL... urls) throws Exception {
		JarLoader.loadJar(urls);
		classLoader = JarLoader.getClassLoader();
	}

	/**
	 * 加载驱动程序
	 * 
	 * @param urls
	 *            驱动URL
	 * @throws Exception
	 */
	public static void loadClass(String... urls) throws Exception {
		if (urls != null) {
			URL[] us = new URL[urls.length];
			for (int i = 0; i < urls.length; i++) {
				us[i] = new File(urls[i]).toURI().toURL();
			}
			loadClass(us);
		}
	}

	/**
	 * 执行sql文件
	 * 
	 * @param conn
	 *            数据库连接
	 * @param sqlFile
	 *            SQL文件
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void executeSQLScript(Connection conn, File sqlFile)
			throws IOException, SQLException {
		if (conn == null) {
			throw new RuntimeException("数据库连接为空");
		}
		if (sqlFile == null || (!sqlFile.exists())) {
			throw new RuntimeException("未找到SQL脚本");
		}
		// Statement stmt = conn.createStatement();
		// String[] sqls = FileUtils.read(sqlFile).trim().split(";");
		// for (int i = 0; i < sqls.length; i++) {
		// if (StringUtils.isNotEmpty(sqls[i])) {
		// // stmt.addBatch(sqls[i]);
		// System.out.println(sqls[i]);
		// stmt.executeUpdate(sqls[i]);
		// }
		// InstallMsgUtils.setInstallProcess((i + 1) / sqls.length);
		// }

		// 使用ibatis的api执行SQL语句
		ScriptRunner runner = new ScriptRunner(conn);
		runner.setAutoCommit(true);//自动提交
		runner.setStopOnError(false);//SQL错误时不停止
		//runner.setFullLineDelimiter(true);//打开行分割符
		runner.setDelimiter("@@");//设置分隔符
		runner.setErrorLogWriter(null);
		runner.setLogWriter(null);
		runner.runScript(new InputStreamReader(new FileInputStream(sqlFile),
				"GBK"));

		// stmt.executeBatch();
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 *            数据库连接
	 */
	public static void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				conn = null;
			}
		}
	}

	public static void main(String[] args) throws Exception {
		loadClass("E:/workspace/ec-setup/com.ec.system.ui/install/lib/jdbc/ojdbc14.jar");
		String url = "jdbc:oracle:thin:@10.6.10.25:1521:autotest";
		String username = "test";
		String password = "123456";
		String driverClass = "oracle.jdbc.driver.OracleDriver";
		File file = new File("E:/workspace/ec-setup/com.ec.system.ui/resource/script/dbscript/Oracle");
		Connection conn = getConnection(url, username, password, driverClass);
		File[] files = file.listFiles();
		List<File> list = new ArrayList<File>();
		for (File file2 : files) {
			list.add(file2);
		}
		Collections.sort(list, new Comparator<File>() {

			@Override
			public int compare(File f1, File f2) {
				String name1 = f1.getName();
				name1 = name1.substring(0, name1.lastIndexOf("."));
				String name2 = f2.getName();
				name2 = name2.substring(0, name2.lastIndexOf("."));
				return name1.compareTo(name2);
			}
		});
		for (File file2 : list) {
			System.out.println("---------------------------"+file2);
			executeSQLScript(conn, file2);
		}
		close(conn);
	}
}
